﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace AutoParkingManagement
{
    class MonthCard
    {
  //      SELECT [ID]
  //    ,[PlateNumber]
  //    ,[PlateTypeId]
  //    ,[CarColorId]
  //    ,[ParkingFloorId]
  //    ,[CustomerId]
  //    ,[FromDate]
  //    ,[ToDate]
  //    ,[Price]
  //    ,[LastUser]
  //    ,[LastUpdate]
  //    ,[Active]
  //FROM [AutoParking].[dbo].[MonthCard]        

        #region Public Properties
        public Guid ID { get; set; }
        public string PlateNumber { get; set; }
        public int PlateTypeId { get; set; }
        public Guid CarColorId { get; set; }
        public Guid ParkingFloorId { get; set; }
        public Guid CustomerId { get; set; }
        public DateTime FromDate { get; set; }
        public DateTime ToDate { get; set; }
        public long Price { get; set; }
        public Guid LastUser { get; set; }
        public DateTime LastUpdate { get; set; }
        public bool Active { get; set; }
        public bool IsExpired { get; set; }       

        #endregion

        public MonthCard()
        {
        }
        public MonthCard GetById(Guid ID)
        {
            string sql = @"SELECT [ID]
                              ,[PlateNumber]
                              ,[PlateTypeId]
                              ,[CarColorId]
                              ,[ParkingFloorId]
                              ,[CustomerId]
                              ,[FromDate]
                              ,[ToDate]
                              ,[Price]
                              ,[LastUser]
                              ,[LastUpdate]
                              ,[Active]
                          WHERE [ID]=@ID";
            SqlParameter parm = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
            parm.Value = ID;
            SqlDataReader objMonthCard= SqlHelper.ExecuteReader(CommandType.Text, sql, parm);
            if (!objMonthCard.HasRows) return null;
            if (objMonthCard.Read())
            {
                this.ID = SqlHelper.CheckGuidNull(objMonthCard["ID"]);
                this.PlateNumber = SqlHelper.CheckStringNull(objMonthCard["PlateNumber"]);
                this.PlateTypeId = SqlHelper.CheckIntNull(objMonthCard["PlateTypeId"]);
                this.CarColorId = SqlHelper.CheckGuidNull(objMonthCard["CarColorId"]);
                this.ParkingFloorId = SqlHelper.CheckGuidNull(objMonthCard["ParkingFloorId"]);
                this.CustomerId = SqlHelper.CheckGuidNull(objMonthCard["CustomerId"]);
                this.FromDate = Convert.ToDateTime(SqlHelper.CheckDateNull(objMonthCard["FromDate"]));
                this.ToDate = Convert.ToDateTime(SqlHelper.CheckDateNull(objMonthCard["ToDate"]));
                this.Price = 0;
                this.LastUser = SqlHelper.CheckGuidNull(objMonthCard["LastUser"]);
                this.LastUpdate = Convert.ToDateTime(SqlHelper.CheckDateNull(objMonthCard["LastUpdate"]));
                this.IsExpired = CheckIsExpiredByPlateNumber(this.PlateNumber);
                return this;
            }
            return null;
        }

        private bool CheckIsExpiredByPlateNumber(string plateNumber)
        {
            string sql = @"SELECT COUNT([ID])
                          FROM MonthCardHistory
                          WHERE PlateNumber=@PlateNumber
                          AND (CAST(GETDATE() AS DATE) 
                                BETWEEN CAST(FromDate AS DATE) AND CAST(ToDate AS DATE))";
            SqlParameter parm = new SqlParameter("@PlateNumber", SqlDbType.NVarChar, 50);
            parm.Value = plateNumber;
            object obj = SqlHelper.ExecuteScalar(CommandType.Text, sql, parm);
            return (Convert.ToInt32(obj)==0);
        }

        public MonthCard GetByPlateNumber(string plateNumber)
        {
            string sql = @"SELECT [ID]
                              ,[PlateNumber]
                              ,[PlateTypeId]
                              ,[CarColorId]
                              ,[ParkingFloorId]
                              ,[CustomerId]
                              ,[FromDate]
                              ,[ToDate]
                              ,[Price]
                              ,[LastUser]
                              ,[LastUpdate]
                              ,[Active]
                          FROM [MonthCard]
                          WHERE Active=1 AND dbo.ufn_StripCharacters(PlateNumber, '^a-z0-9')=dbo.ufn_StripCharacters(@PlateNumber, '^a-z0-9')";
            SqlParameter parm = new SqlParameter("@PlateNumber", SqlDbType.NVarChar,50);
            parm.Value = plateNumber;
            SqlDataReader objMonthCard = SqlHelper.ExecuteReader(CommandType.Text, sql, parm);
            if (!objMonthCard.HasRows) return null;
            if (objMonthCard.Read())
            {
                this.ID = SqlHelper.CheckGuidNull(objMonthCard["ID"]);
                this.PlateNumber = SqlHelper.CheckStringNull(objMonthCard["PlateNumber"]);
                this.PlateTypeId = SqlHelper.CheckIntNull(objMonthCard["PlateTypeId"]);
                this.CarColorId = SqlHelper.CheckGuidNull(objMonthCard["CarColorId"]);
                this.ParkingFloorId = SqlHelper.CheckGuidNull(objMonthCard["ParkingFloorId"]);
                this.CustomerId = SqlHelper.CheckGuidNull(objMonthCard["CustomerId"]);
                this.FromDate = Convert.ToDateTime(SqlHelper.CheckDateNull(objMonthCard["FromDate"]));
                this.ToDate = Convert.ToDateTime(SqlHelper.CheckDateNull(objMonthCard["ToDate"]));
                this.Price = 0;
                this.LastUser = SqlHelper.CheckGuidNull(objMonthCard["LastUser"]);
                this.LastUpdate = Convert.ToDateTime(SqlHelper.CheckDateNull(objMonthCard["LastUpdate"]));
                this.IsExpired = CheckIsExpiredByPlateNumber(this.PlateNumber);
                return this;
            }
            return null;
        }
    }
}
